[CHAPTER 02. PostgreSQL Locks]

2. Relation Lock
2-2. Relation Lock의 Mode

DROP TABLE IF EXISTS relation_test;
CREATE TABLE relation_test (
    a INTEGER PRIMARY KEY,
    b TEXT,
    c NUMERIC
);

INSERT INTO relation_test 
VALUES
    (1, 'row1', 100.00),
    (2, 'row2', 200.00),
    (3, 'row3', 300.00);

/* [CASE 1] 일반 CREATE INDEX
   - pid 2306020: 인덱스 생성
   - pid 2309053: relation_test 데이터 업데이트 */
   
/* 세션 1 (pid: 2306020) */    
CREATE INDEX relation_test_idx ON relation_test(a);

/* 세션 2 (pid: 2309053) */
UPDATE   relation_test
SET      a = 5
WHERE    a = 6;

SELECT   pid,
         locktype,
         CASE locktype
              WHEN 'relation'      THEN relation::regclass::text
              WHEN 'transactionid' THEN transactionid::text
              WHEN 'virtualxid'    THEN virtualxid
              WHEN 'tuple'         THEN relation::regclass::text||'('||page||','||tuple||')'
              WHEN 'page'          THEN relation::regclass::text||'('||page||')'
         END AS lockid,
         mode,
         granted
FROM     pg_locks
WHERE    pid IN (2306020, 2309053)
ORDER BY pid, locktype, lockid;    

/* [CASE 2] CREATE INDEX CONCURRENTLY
   - pid 2306020: CONCURRENTLY 인덱스 생성
   - pid 2309053: relation_test 데이터 업데이트 */
   
/* 세션 1 (pid: 2306020) */
CREATE INDEX CONCURRENTLY relation_test_idx ON relation_test(a);

/* 세션 2 (pid: 2309053) */
UPDATE   relation_test
SET      a = 5
WHERE    a = 6;

SELECT   pid,
         locktype,
         CASE locktype
              WHEN 'relation'      THEN relation::regclass::text
              WHEN 'transactionid' THEN transactionid::text
              WHEN 'virtualxid'    THEN virtualxid
              WHEN 'tuple'         THEN relation::regclass::text||'('||page||','||tuple||')'
              WHEN 'page'          THEN relation::regclass::text||'('||page||')'
         END AS lockid,
         mode,
         granted
FROM     pg_locks
WHERE    pid IN (2306020, 2309053)
ORDER BY pid, locktype, lockid;

/* [CASE 1] 일반 REFRESH MATERIALIZED VIEW
   - pid 2316254: relation_view 갱신
   - pid 2316478: relation_view 데이터 조회 */
   
/* 세션 1 (pid: 2316254) */
REFRESH MATERIALIZED VIEW relation_view;

/* 세션 2 (pid: 2316478) */
SELECT   *
FROM     relation_view;

SELECT   pid,
         locktype,
         CASE locktype
              WHEN 'relation'      THEN relation::regclass::text
              WHEN 'transactionid' THEN transactionid::text
              WHEN 'virtualxid'    THEN virtualxid
              WHEN 'tuple'         THEN relation::regclass::text||'('||page||','||tuple||')'
              WHEN 'page'          THEN relation::regclass::text||'('||page||')'
         END AS lockid,
         mode,
         granted
FROM     pg_locks
WHERE    pid IN (2316254, 2316478)
ORDER BY pid, locktype, lockid;

/* [CASE 2] REFRESH MATERIALIZED VIEW CONCURRENTLY
   - pid 2316254: relation_view를 CONCURRENTLY 모드로 갱신
   - pid 2316478: relation_view 데이터 조회 */
   
/* 세션 1 (pid: 2316254) */
REFRESH MATERIALIZED VIEW CONCURRENTLY relation_view;

/* 세션 2 (pid: 2316478) */
SELECT   *
FROM     relation_view;

SELECT   pid,
         locktype,
         CASE locktype
              WHEN 'relation'      THEN relation::regclass::text
              WHEN 'transactionid' THEN transactionid::text
              WHEN 'virtualxid'    THEN virtualxid
              WHEN 'tuple'         THEN relation::regclass::text||'('||page||','||tuple||')'
              WHEN 'page'          THEN relation::regclass::text||'('||page||')'
         END AS lockid,
         mode,
         granted
FROM     pg_locks
WHERE    pid IN (2316254, 2316478)
ORDER BY pid, locktype, lockid;

/* LOCK TABLE */
LOCK TABLE relation_test;

SELECT   pid,
         locktype,
         CASE locktype
              WHEN 'relation'      THEN relation::regclass::text
              WHEN 'transactionid' THEN transactionid::text
              WHEN 'virtualxid'    THEN virtualxid
              WHEN 'tuple'         THEN relation::regclass::text||'('||page||','||tuple||')'
              WHEN 'page'          THEN relation::regclass::text||'('||page||')'
         END AS lockid,
         mode,
         granted
FROM     pg_locks
WHERE    pid IN (2300463)
ORDER BY pid, locktype, lockid;


2-3. TransactionID Lock


SELECT   pid,
         locktype,
         CASE locktype
              WHEN 'relation'      THEN relation::regclass::text
              WHEN 'transactionid' THEN transactionid::text
              WHEN 'virtualxid'    THEN virtualxid
              WHEN 'tuple'         THEN relation::regclass::text||'('||page||','||tuple||')'
              WHEN 'page'          THEN relation::regclass::text||'('||page||')'
         END AS lockid,
         mode,
         granted
FROM     pg_locks
WHERE    pid IN (2316254)
ORDER BY pid, locktype, lockid;

/* Virtual TransactionID */
SELECT   pid,
         locktype,
         CASE locktype
              WHEN 'relation'      THEN relation::regclass::text
              WHEN 'transactionid' THEN transactionid::text
              WHEN 'virtualxid'    THEN virtualxid
              WHEN 'tuple'         THEN relation::regclass::text||'('||page||','||tuple||')'
              WHEN 'page'          THEN relation::regclass::text||'('||page||')'
         END AS lockid,
         mode,
         granted
FROM     pg_locks
WHERE    pid IN (2316254)
ORDER BY pid, locktype, lockid;

/* TransactionID Lock */
SELECT   pid,
         locktype,
         CASE locktype
              WHEN 'relation'      THEN relation::regclass::text
              WHEN 'transactionid' THEN transactionid::text
              WHEN 'virtualxid'    THEN virtualxid
              WHEN 'tuple'         THEN relation::regclass::text||'('||page||','||tuple||')'
              WHEN 'page'          THEN relation::regclass::text||'('||page||')'
         END AS lockid,
         mode,
         granted
FROM     pg_locks
WHERE    pid IN (1162505, 1164406)
ORDER BY pid, locktype, lockid;


2-4. Relation Lock Contention


/* Relation Lock Mode 간 호환이 안될 때 */
/* 트랜잭션 1 */
BEGIN;
SELECT   txid_current(),
         pg_backend_pid();

UPDATE   relation_test
SET      c = c + 100
WHERE    a = 1;

/* 트랜잭션 2 */
BEGIN;
SELECT   txid_current(),
         pg_backend_pid();
         
CREATE INDEX relation_test_idx ON relation_test(b);

SELECT   pid,
         locktype,
         CASE locktype
              WHEN 'relation'      THEN relation::regclass::text
              WHEN 'transactionid' THEN transactionid::text
              WHEN 'virtualxid'    THEN virtualxid
              WHEN 'tuple'         THEN relation::regclass::text||'('||page||','||tuple||')'
              WHEN 'page'          THEN relation::regclass::text||'('||page||')'
         END AS lockid,
         mode,
         granted
FROM     pg_locks
WHERE    pid IN (2487936, 2487939)
ORDER BY pid, locktype, lockid;

/* Relation Lock Mode간 호환이 될 때 */
/* 트랜잭션 1 */
BEGIN;
SELECT   txid_current(),
         pg_backend_pid();

UPDATE   relation_test
SET      c = c + 100.00
WHERE    a = 1;

/* 트랜잭션 2 */
BEGIN;
SELECT   txid_current(),
         pg_backend_pid();

SELECT   *
FROM     relation_test
WHERE    a = 1
FOR UPDATE;

SELECT   pid,
         locktype,
         CASE locktype
              WHEN 'relation'      THEN relation::regclass::text
              WHEN 'transactionid' THEN transactionid::text
              WHEN 'virtualxid'    THEN virtualxid
              WHEN 'tuple'         THEN relation::regclass::text||'('||page||','||tuple||')'
              WHEN 'page'          THEN relation::regclass::text||'('||page||')'
         END AS lockid,
         mode,
         granted
FROM     pg_locks
WHERE    pid IN (2493574, 2493575)
ORDER BY pid, locktype, lockid;


3.  Row-Level Lock
3-2. Row-Level Lock의 Mode


/* pageinspect Extension으로 Row-Level Lock Mode 확인하기 */
CREATE EXTENSION pageinspect;

DROP FUNCTION IF EXISTS heap_page(TEXT, INTEGER);
CREATE FUNCTION heap_page(relname TEXT, pageno INTEGER)
RETURNS TABLE (
    ctid tid,
    xmin TEXT,
    xmax TEXT,
    lock_only TEXT,
    is_multi TEXT,
    hot_updated TEXT,
    keys_updated TEXT,
    keyshr_lock TEXT,
    shr_lock TEXT,
    t_ctid tid
)
AS $$
SELECT   (pageno, lp)::TEXT::tid AS ctid,  
         t_xmin || CASE 
                       WHEN (t_infomask & 256) > 0 THEN '(c)'  
                       WHEN (t_infomask & 512) > 0 THEN '(a)'  
                       ELSE '' 
                  END AS xmin, 
         t_xmax || CASE 
                       WHEN (t_infomask & 1024) > 0 THEN '(c)'  
                       WHEN (t_infomask & 2048) > 0 THEN '(a)'  
                       ELSE '' 
                  END AS xmax,
        CASE WHEN t_infomask & 128 = 128         
				THEN 'T' END AS lock_only,              
        CASE WHEN t_infomask & 4096 = 4096       
				THEN 'T' END AS is_multi,              
        CASE WHEN t_infomask2 & 16384 = 16384    
				THEN 'T' END AS hot_updated,           
        CASE WHEN t_infomask2 & 8192 = 8192      
				THEN 'T' END AS keys_updated,          
        CASE WHEN t_infomask & 16 = 16           
				THEN 'T' END AS keyshr_lock,            
        CASE WHEN t_infomask & (16 + 64) = (16 + 64) 
				THEN 'T' END AS shr_lock,              
         t_ctid
FROM     heap_page_items(get_raw_page(relname, pageno))
ORDER BY lp;
$$ LANGUAGE sql;

DROP TABLE IF EXISTS row_test;
CREATE TABLE row_test (
    a INTEGER PRIMARY KEY,
    b TEXT,
    c NUMERIC
);

INSERT INTO row_test 
VALUES 
    (1, 'row1', 100.00),
    (2, 'row2', 200.00),
    (3, 'row3', 300.00);
    
SELECT   *
FROM     row_test;    

/* UPDATE */
UPDATE   row_test
SET      a = 20
WHERE    a = 1;

UPDATE   row_test
SET      c = c + 100.00
WHERE    a = 2;

SELECT   *
FROM     heap_page('row_test', 0);

/* SELECT FOR UPDATE */
SELECT   *
FROM     row_test
WHERE    a = 1
FOR UPDATE;

SELECT   *
FROM     row_test
WHERE    a = 2
FOR NO KEY UPDATE;

SELECT   *
FROM     heap_page('row_test', 0);

/* SELECT FOR SHARE */
SELECT   *
FROM     row_test
WHERE    a = 1
FOR SHARE;

SELECT   *
FROM     row_test
WHERE    a = 2
FOR KEY SHARE;

SELECT   *
FROM     heap_page('row_test', 0);


3-3. Tuple Lock의 Mode


CREATE EXTENSION pgrowlocks;
DROP TABLE IF EXISTS tuple_test;
CREATE TABLE tuple_test (
    a INTEGER PRIMARY KEY,
    b TEXT,
    c NUMERIC(18,2) NOT NULL DEFAULT 0
);

INSERT INTO tuple_test (a, b, c)
SELECT   gs,
         'pad',
         0
FROM     generate_series(1, 200) AS gs;

/* 트랜잭션 1 */
UPDATE   tuple_test
SET      a = 201
WHERE    a = 1;

/* 트랜잭션 2 */
UPDATE   tuple_test
SET      a = 201
WHERE    a = 1;

/* 트랜잭션 3 */
UPDATE   tuple_test
SET      c = c + 100.00
WHERE    a = 1;

/* 트랜잭션 4 */
SELECT   *
FROM     tuple_test
WHERE    a = 1
FOR SHARE;

/* 트랜잭션 5 */
SELECT   *
FROM     tuple_test
WHERE    a = 1
FOR KEY SHARE;

SELECT   *
FROM     pgrowlocks('tuple_test');

SELECT   pid,
         locktype,
         CASE locktype
              WHEN 'relation'      THEN relation::regclass::text
              WHEN 'transactionid' THEN transactionid::text
              WHEN 'virtualxid'    THEN virtualxid
              WHEN 'tuple'         THEN relation::regclass::text||'('||page||','||tuple||')'
              WHEN 'page'          THEN relation::regclass::text||'('||page||')'
         END AS lockid,
         mode,
         granted
FROM     pg_locks
WHERE    locktype = 'tuple'
ORDER BY pid, locktype, lockid;

/* pgrowlocks Extension에서의 Row-Level Lock Mode */
BEGIN;

/* ctid(0,1) */
SELECT   *
FROM     tuple_test
WHERE    a = 1
FOR KEY SHARE;

/* ctid(0,2) */
SELECT   *
FROM     tuple_test
WHERE    a = 2
FOR SHARE;

/* ctid(0,3) */
SELECT   *
FROM     tuple_test
WHERE    a = 3
FOR NO KEY UPDATE;

/* ctid(0,4) */
SELECT   *
FROM     tuple_test
WHERE    a = 4
FOR UPDATE;

/* ctid(0,5) */
UPDATE   tuple_test
SET      c = c + 100.00
WHERE    a = 5;

/* ctid(0,6) */
UPDATE   tuple_test
SET      a = 201
WHERE    a = 6;

SELECT   *
FROM     pgrowlocks('tuple_test');


3-4. Row-Level Lock Contention


/* Row-Level Lock Mode가 호환 불가능할 때
   - 트랜잭션 1(T1) : UPDATE 수행 
   - 트랜잭션 2(T2) : SELECT FOR UPDATE 수행
   - 트랜잭션 3(T3) : SELECT FOR FOR SHARE 수행 */
   
   
/* 트랜잭션 1 */
BEGIN;
SELECT   pg_backend_pid(),
         txid_current();
         
UPDATE   row_test
SET      c = c + 100
WHERE    a = 1;         

SELECT   pid,
         locktype,
         CASE locktype
              WHEN 'relation'      THEN relation::regclass::text
              WHEN 'transactionid' THEN transactionid::text
              WHEN 'virtualxid'    THEN virtualxid
              WHEN 'tuple'         THEN relation::regclass::text||'('||page||','||tuple||')'
              WHEN 'page'          THEN relation::regclass::text||'('||page||')'
         END AS lockid,
         mode,
         granted
FROM     pg_locks
WHERE    pid IN (2493574)
ORDER BY pid, locktype, lockid;

SELECT   *
FROM     pgrowlocks('row_test');

SELECT   *
FROM     heap_page('row_test', 0);

/* 트랜잭션 2 */
BEGIN;
SELECT   pg_backend_pid(),
         txid_current();
         
SELECT   *
FROM     row_test
WHERE    a = 1
FOR UPDATE;         

SELECT   pid,
         locktype,
         CASE locktype
              WHEN 'relation'      THEN relation::regclass::text
              WHEN 'transactionid' THEN transactionid::text
              WHEN 'virtualxid'    THEN virtualxid
              WHEN 'tuple'         THEN relation::regclass::text||'('||page||','||tuple||')'
              WHEN 'page'          THEN relation::regclass::text||'('||page||')'
         END AS lockid,
         mode,
         granted
FROM     pg_locks
WHERE    pid IN (2493574, 2493575)
ORDER BY pid, locktype, lockid;

SELECT   *
FROM     heap_page('row_test', 0);

/* 트랜잭션 3 */
BEGIN;
SELECT   pg_backend_pid(),
         txid_current();

SELECT   *
FROM     row_test
WHERE    a = 1
FOR SHARE;

SELECT   pid,
         locktype,
         CASE locktype
              WHEN 'relation'      THEN relation::regclass::text
              WHEN 'transactionid' THEN transactionid::text
              WHEN 'virtualxid'    THEN virtualxid
              WHEN 'tuple'         THEN relation::regclass::text||'('||page||','||tuple||')'
              WHEN 'page'          THEN relation::regclass::text||'('||page||')'
         END AS lockid,
         mode,
         granted
FROM     pg_locks
WHERE    pid IN (3790257)
ORDER BY pid, locktype, lockid;



/* Row-Level Lock Mode 호환 가능할 때
   - 트랜잭션 1(T1) : UPDATE 수행 
   - 트랜잭션 2(T2) : SELECT FOR KEY SHARE 수행 */
   
   
/* 트랜잭션 1 */
BEGIN;
SELECT   pg_backend_pid(),
         txid_current();
         
UPDATE   row_test
SET      c = c + 100.00
WHERE    a = 1;         

SELECT   pid,
         locktype,
         CASE locktype
              WHEN 'relation'      THEN relation::regclass::text
              WHEN 'transactionid' THEN transactionid::text
              WHEN 'virtualxid'    THEN virtualxid
              WHEN 'tuple'         THEN relation::regclass::text||'('||page||','||tuple||')'
              WHEN 'page'          THEN relation::regclass::text||'('||page||')'
         END AS lockid,
         mode,
         granted
FROM     pg_locks
WHERE    pid IN (2493574)
ORDER BY pid, locktype, lockid;

SELECT   *
FROM     pgrowlocks('row_test');

SELECT   *
FROM     heap_page('row_test', 0);

/* 트랜잭션 2 */
BEGIN;
SELECT   pg_backend_pid(),
         txid_current();
         
SELECT   *
FROM     row_test
WHERE    a = 1
FOR KEY SHARE;         

SELECT   pid,
         locktype,
         CASE locktype
              WHEN 'relation'      THEN relation::regclass::text
              WHEN 'transactionid' THEN transactionid::text
              WHEN 'virtualxid'    THEN virtualxid
              WHEN 'tuple'         THEN relation::regclass::text||'('||page||','||tuple||')'
              WHEN 'page'          THEN relation::regclass::text||'('||page||')'
         END AS lockid,
         mode,
         granted
FROM     pg_locks
WHERE    pid IN (2493574, 2493575)
ORDER BY pid, locktype, lockid;

SELECT   *
FROM     pgrowlocks('row_test');

SELECT   *
FROM     heap_page('row_test', 0);


3-5. Row-Level Lock과 Dead Lock


SHOW deadlock_timeout;

DROP TABLE IF EXISTS lock_test;
CREATE TABLE lock_test (
    a INTEGER PRIMARY KEY,
    b TEXT,
    c NUMERIC
);

INSERT INTO lock_test 
VALUES 
    (1, 'row1', 100.00),
    (2, 'row2', 200.00),
    (3, 'row3', 300.00);
    
    
/* 트랜잭션 1 */
BEGIN; 
SELECT   pg_backend_pid(),
         txid_current();    
         
SELECT   *
FROM     lock_test
WHERE    b = 'row1'
FOR UPDATE;

UPDATE   lock_test
SET      a = a + 1000
WHERE    b = 'row2';

/* 트랜잭션 2 */
BEGIN;
SELECT   pg_backend_pid(),
         txid_current();         
         
SELECT   *
FROM     lock_test
WHERE    b = 'row2'
FOR UPDATE;         

UPDATE   lock_test
SET      a = a + 1000
WHERE    b = 'row1';

SELECT   datid,
         datname,
         deadlocks
FROM     pg_stat_database;


4. Predicate Lock
4-1. Predicate Lock의 동작 원리


CREATE TABLE pred_test AS
SELECT   no AS a,
         md5(random()::text) AS b
FROM     pg_catalog.generate_series(1, 10000) AS no;

/* Sequential Scan */
BEGIN ISOLATION LEVEL SERIALIZABLE;

EXPLAIN (ANALYZE, COSTS OFF)
SELECT   *
FROM     pred_test
WHERE    a > 100;

SELECT   pid,
         locktype,
         CASE locktype
              WHEN 'relation'      THEN relation::regclass::text
              WHEN 'transactionid' THEN transactionid::text
              WHEN 'virtualxid'    THEN virtualxid
              WHEN 'tuple'         THEN relation::regclass::text||'('||page||','||tuple||')'
              WHEN 'page'          THEN relation::regclass::text||'('||page||')'
         END AS lockid,
         mode,
         granted
FROM     pg_locks
WHERE    mode = 'SIReadLock'
ORDER BY pid, locktype, lockid;

/* Index Scan */
CREATE INDEX pred_idx ON pred_test (a);

BEGIN ISOLATION LEVEL SERIALIZABLE;

EXPLAIN (ANALYZE, COSTS OFF)
SELECT   *
FROM     pred_test
WHERE    a BETWEEN 1000 AND 1001;

SELECT   pid,
         locktype,
         CASE locktype
              WHEN 'relation'      THEN relation::regclass::text
              WHEN 'transactionid' THEN transactionid::text
              WHEN 'virtualxid'    THEN virtualxid
              WHEN 'tuple'         THEN relation::regclass::text||'('||page||','||tuple||')'
              WHEN 'page'          THEN relation::regclass::text||'('||page||')'
         END AS lockid,
         mode,
         granted,
         page,
         tuple
FROM     pg_locks
WHERE    mode = 'SIReadLock'
ORDER BY pid, locktype, lockid;

/* Lock Escalation */
SHOW max_pred_locks_per_page;

EXPLAIN (ANALYZE, COSTS OFF)
SELECT   *
FROM     pred_test
WHERE    a BETWEEN 1000 AND 1002;

SELECT   pid,
         locktype,
         CASE locktype
              WHEN 'relation'      THEN relation::regclass::text
              WHEN 'transactionid' THEN transactionid::text
              WHEN 'virtualxid'    THEN virtualxid
              WHEN 'tuple'         THEN relation::regclass::text||'('||page||','||tuple||')'
              WHEN 'page'          THEN relation::regclass::text||'('||page||')'
         END AS lockid,
         mode,
         granted
FROM     pg_locks
WHERE    mode = 'SIReadLock'
ORDER BY pid, locktype, lockid;

/* Predicate Lock과 Read/Write Dependency */
DROP TABLE IF EXISTS exem;

CREATE TABLE exem (
    group_name TEXT,
    name       TEXT
);

INSERT INTO exem (group_name, name)
VALUES 
    ('exem', 'Zoey'),
    ('exem', 'Lisa'),
    ('exem', 'Tom'),
    ('exem', 'Kai');
    
SELECT   *
FROM     exem;   

/* 트랜잭션 1 */
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT   COUNT(*)
FROM     exem;

INSERT INTO exem
VALUES ('exem', 'John');

COMMIT;

/* 트랜잭션 2 */
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT   COUNT(*)
FROM     exem; 

INSERT INTO exem
VALUES ('exem', 'Ellie');

COMMIT;


5. Other Locks

5-1. Non-relation Lock


BEGIN;

CREATE TABLE object_test (
    n INTEGER
);

SELECT   database,
         (SELECT datname FROM pg_database WHERE oid = database) AS dbname,
         classid,
         (SELECT relname FROM pg_class WHERE oid = classid) AS classname,
         objid,
         mode,
         granted
FROM     pg_locks
WHERE    locktype = 'object';

SELECT   nspname
FROM     pg_namespace
WHERE    oid = 2200;


5-4. Advisory Lock


DROP TABLE IF EXISTS adv_test;
CREATE TABLE adv_test (
    a INTEGER,
    b TEXT
);

INSERT INTO adv_test
VALUES
    (1, 'insert1'),
    (2, 'insert2');  
    
    
/* 트랜잭션 1 */
BEGIN;
UPDATE adv_test
SET    b = 'update1'
WHERE  a = 1
RETURNING pg_try_advisory_xact_lock(a);

/* 트랜잭션 2 */
BEGIN;
SELECT   *
FROM     adv_test
WHERE    a = 1;

SELECT   *
FROM     adv_test
WHERE    a = 1
AND      pg_try_advisory_xact_lock(a);
